/***************************************************************
 *  Copyright (c) 2007 by GroupMe! Team (www.groupme.net)
 *  All rights reserved
 *
 *  This file is part of the GroupMe! Project. You can utilize
 *  software of the GroupMe! project under the terms of the 
 *  GNU Lesser General Public License as published by the
 *  Free Software Foundation in version 2.1.
 * 
 *  The GNU Lesser General Public License can be found at:
 *  http://www.gnu.org/licenses/lgpl.html
 *
 *  This copyright notice MUST APPEAR in all copies of the file!
 ***************************************************************/
package net.groupme.model.dao;

import java.net.URI;
import java.net.URISyntaxException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;

import javax.sql.DataSource;

import net.groupme.exceptions.DeleteFailedException;
import net.groupme.exceptions.InsertFailedException;
import net.groupme.exceptions.UpdateFailedException;
import net.groupme.model.Group;
import net.groupme.model.GroupImpl;
import net.groupme.model.ResourceVisualization;
import net.groupme.model.Style;
import net.groupme.model.StyleImpl;
import net.groupme.model.User;
import net.groupme.utils.sql.GetResourceVisualizationsQuery;
import net.groupme.utils.sql.GetStringFromTableWhereIDQuery;
import net.groupme.utils.sql.GetStringQuery;
import net.groupme.utils.sql.GetUsersQuery;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.SqlUpdate;
import org.springframework.jdbc.support.GeneratedKeyHolder;

/**
 * 
 * The class/interface GroupVisualizationDAOImpl ...
 * 
 * @author Mischa Frank <a
 *         href="mailto:code@planetfrank.net">code@planetfrank.net</a>
 * 
 * @version 1.0, created on 24.04.2007
 * 
 */
public class GroupVisualizationDAOImpl implements GroupVisualizationDAO {

	// table
	private static final String TABLE_VISUALIZATION = DBProperties
			.getString("GroupmeDB.TABLE_VISUALIZATION"); //$NON-NLS-1$

	// columns
	private static final String VISUALIZATION_ID = DBProperties
			.getString("GroupmeDB.VISUALIZATION_ID"); //$NON-NLS-1$

	private static final String VISUALIZATION_NAME = DBProperties
			.getString("GroupmeDB.VISUALIZATION_NAME"); //$NON-NLS-1$

	private static final String VISUALIZATION_THUMBNAIL = DBProperties
			.getString("GroupmeDB.VISUALIZATION_THUMBNAIL");
	
	private static final String VISUALIZATION_DESCRIPTION = DBProperties
			.getString("GroupmeDB.VISUALIZATION_DESCRIPTION"); //$NON-NLS-1$

	// table
	private static final String TABLE_RESOURCEVISUALIZATION = DBProperties
			.getString("GroupmeDB.TABLE_RESOURCEVISUALIZATION"); //$NON-NLS-1$

	// columns
	private static final String RESOURCEVISUALIZATION_ID = DBProperties
			.getString("GroupmeDB.RESOURCEVISUALIZATION_ID"); //$NON-NLS-1$

	private static final String RESOURCEVISUALIZATION_VISUALIZATIONID = DBProperties
			.getString("GroupmeDB.RESOURCEVISUALIZATION_VISUALIZATIONID"); //$NON-NLS-1$

	// table
	private static final String TABLE_VISUALIZATIONGROUPSTYLE = DBProperties
			.getString("GroupmeDB.TABLE_VISUALIZATIONGROUPSTYLE"); //$NON-NLS-1$

	// columns
	private static final String VISUALIZATIONGROUPSTYLE_VISUALIZATIONID = DBProperties
			.getString("GroupmeDB.VISUALIZATIONGROUPSTYLE_VISUALIZATIONID"); //$NON-NLS-1$

	private static final String VISUALIZATIONGROUPSTYLE_GROUPSTYLEID = DBProperties
			.getString("GroupmeDB.VISUALIZATIONGROUPSTYLE_GROUPSTYLEID"); //$NON-NLS-1$

	// table
	private static final String TABLE_USERGROUPVISUALIZATION = DBProperties
			.getString("GroupmeDB.TABLE_USERGROUPVISUALIZATION"); //$NON-NLS-1$

	// columns
	private static final String USERGROUPVISUALIZATION_VISUALIZATION_ID = DBProperties
			.getString("GroupmeDB.USERGROUPVISUALIZATION_VISUALIZATION_ID"); //$NON-NLS-1$

	private static final String USERGROUPVISUALIZATION_USER_ID = DBProperties
			.getString("GroupmeDB.USERGROUPVISUALIZATION_USER_ID"); //$NON-NLS-1$

	private static final String USERGROUPVISUALIZATION_GROUP_ID = DBProperties
			.getString("GroupmeDB.USERGROUPVISUALIZATION_GROUP_ID"); //$NON-NLS-1$

	private static final String USERGROUPVISUALIZATION_ROLE = DBProperties
			.getString("GroupmeDB.USERGROUPVISUALIZATION_ROLE"); //$NON-NLS-1$

	private static final String USERGROUPVISUALIZATION_ACTUAL = DBProperties
			.getString("GroupmeDB.USERGROUPVISUALIZATION_ACTUAL"); //$NON-NLS-1$

	private static final Logger log = Logger
			.getLogger(GroupVisualizationDAOImpl.class);

	private DataSource ds;

	private String id;

	private String query;

	public GroupVisualizationDAOImpl(DataSource ds, String id) {
		this.ds = ds;
		this.id = id;
	}

	public GroupVisualizationDAOImpl(DataSource ds, String name, String description) throws InsertFailedException {
		this.ds = ds;
		SqlUpdate su = new SqlUpdate(ds, "INSERT INTO " + TABLE_VISUALIZATION//$NON-NLS-1$
				+ " (" + VISUALIZATION_NAME + ", " + VISUALIZATION_DESCRIPTION + ") VALUES (?, ?)");
		su.declareParameter(new SqlParameter("name", Types.VARCHAR)); //$NON-NLS-1$
		su.declareParameter(new SqlParameter("description", Types.VARCHAR)); //$NON-NLS-1$
		su.compile();
		Object[] oa = new Object[2];
		oa[0] = new String(name);
		oa[1] = new String(description);
		GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
		int count = su.update(oa, keyHolder);
		if (count == 0)
			throw new InsertFailedException();
		this.id = keyHolder.getKey().toString();
	}
	
	/* (non-Javadoc)
	 * @see net.groupme.model.Identifiable#getId()
	 */
	public String getId() {
		return this.id;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see net.groupme.model.GroupVisualization#addResourceVisualization(net.groupme.model.ResourceVisualization)
	 */
	public void addResourceVisualization(
			ResourceVisualization resourceVisualization)
			throws UpdateFailedException {
		resourceVisualization.setGroupVisualization(this);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see net.groupme.model.GroupVisualization#addUser(java.lang.String,
	 *      net.groupme.model.User)
	 */
	public void addUser(String role, User user) throws InsertFailedException {
		SqlUpdate su = new SqlUpdate(
				ds,
				"INSERT INTO " + TABLE_USERGROUPVISUALIZATION //$NON-NLS-1$
						+ " ("
						+ USERGROUPVISUALIZATION_USER_ID
						+ ", "
						+ USERGROUPVISUALIZATION_GROUP_ID
						+ ", "
						+ USERGROUPVISUALIZATION_VISUALIZATION_ID
						+ ", "
						+ USERGROUPVISUALIZATION_ROLE
						+ ", " + USERGROUPVISUALIZATION_ACTUAL + ") VALUES (?, ?, ?, ?, ?)"); //$NON-NLS-1$ //$NON-NLS-2$ 
		su.declareParameter(new SqlParameter("uID", Types.INTEGER)); //$NON-NLS-1$
		su.declareParameter(new SqlParameter("gID", Types.INTEGER)); //$NON-NLS-1$
		su.declareParameter(new SqlParameter("vID", Types.INTEGER)); //$NON-NLS-1$
		su.declareParameter(new SqlParameter("role", Types.VARCHAR)); //$NON-NLS-1$
		su.declareParameter(new SqlParameter("actual", Types.BOOLEAN)); //$NON-NLS-1$
		su.compile();
		try {
			Object[] oa = new Object[5];
			oa[0] = new Integer(user.getId());
			oa[1] = new Integer(this.getGroup().getId());
			oa[2] = new Integer(this.id);
			oa[3] = new String(role);
			oa[4] = new Boolean(false);
			int count = su.update(oa);
			if (count == 0)
				throw new InsertFailedException();
		} catch (NumberFormatException e) {
			log.debug(e.getMessage());
		} 
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see net.groupme.model.Identifiable#getDescription()
	 */
	public String getDescription() {
		GetStringFromTableWhereIDQuery getDescriptionQuery = new GetStringFromTableWhereIDQuery(
				ds, VISUALIZATION_DESCRIPTION, TABLE_VISUALIZATION, id);
		return (String) getDescriptionQuery.execute().iterator().next();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see net.groupme.model.GroupVisualization#getGroup()
	 */
	public Group getGroup() {
		query = "SELECT " + USERGROUPVISUALIZATION_GROUP_ID + " FROM " + TABLE_USERGROUPVISUALIZATION//$NON-NLS-1$ //$NON-NLS-2$
				+ " WHERE " + USERGROUPVISUALIZATION_VISUALIZATION_ID + " = " + id; //$NON-NLS-1$ //$NON-NLS-2$
		GetStringQuery getStringQuery = new GetStringQuery(ds, query,
				USERGROUPVISUALIZATION_GROUP_ID);
		Group group = new GroupImpl(new GroupDAOImpl(ds,
				(String) getStringQuery.execute().iterator().next()));
		return group;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see net.groupme.model.Identifiable#getLabel()
	 */
	public String getLabel() {
		return this.getName() + ", Visualization of Group: "
				+ this.getGroup().getLabel();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see net.groupme.model.GroupVisualization#getName()
	 */
	public String getName() {
		GetStringFromTableWhereIDQuery getDescriptionQuery = new GetStringFromTableWhereIDQuery(
				ds, VISUALIZATION_NAME, TABLE_VISUALIZATION, id);
		return (String) getDescriptionQuery.execute().iterator().next();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see net.groupme.model.GroupVisualization#getResourceVisualizations()
	 */
	@SuppressWarnings("unchecked")
	public Collection<ResourceVisualization> getResourceVisualizations() {
		query = "SELECT " + RESOURCEVISUALIZATION_ID + " FROM " + TABLE_RESOURCEVISUALIZATION + " WHERE " + RESOURCEVISUALIZATION_VISUALIZATIONID + " = " + id; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
		GetResourceVisualizationsQuery getResourceVisualizationsQuery = new GetResourceVisualizationsQuery(
				ds, query, RESOURCEVISUALIZATION_ID);
		Iterator iter = getResourceVisualizationsQuery.execute().iterator();
		if (iter.hasNext()){	
			return (Collection<ResourceVisualization>) iter.next();
			}
			return new ArrayList<ResourceVisualization>();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see net.groupme.model.GroupVisualization#getStyle()
	 */
	public Style getStyle() {
		query = "SELECT " + VISUALIZATIONGROUPSTYLE_GROUPSTYLEID
				+ " FROM "
				+ TABLE_VISUALIZATIONGROUPSTYLE//$NON-NLS-1$ //$NON-NLS-2$
				+ " WHERE " + VISUALIZATIONGROUPSTYLE_VISUALIZATIONID + " = "
				+ id;
		//$NON-NLS-1$ //$NON-NLS-2$
		GetStringQuery getStringQuery = new GetStringQuery(ds, query,
				VISUALIZATIONGROUPSTYLE_GROUPSTYLEID);
		Style style = new StyleImpl(new StyleDAOImpl(ds,
				(String) getStringQuery.execute().iterator().next()));
		return style;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see net.groupme.model.Identifiable#getURI()
	 */
	public URI getURI(){
		try {
			return new URI("http://www.groupme.net/groupvisualization/" + id); //$NON-NLS-1$
		} catch (URISyntaxException e) {
			e.printStackTrace();
			return null;
		} 
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see net.groupme.model.GroupVisualization#getUser(java.lang.String)
	 */
	@SuppressWarnings("unchecked")
	public Collection<User> getUser(String role) {
		query = "SELECT " + USERGROUPVISUALIZATION_USER_ID + " FROM "
				+ TABLE_USERGROUPVISUALIZATION + " WHERE "
				+ USERGROUPVISUALIZATION_VISUALIZATION_ID + " = " + id
				+ " AND " + USERGROUPVISUALIZATION_ROLE + " = " + role;
		GetUsersQuery getUsersQuery = new GetUsersQuery(ds, query,
				USERGROUPVISUALIZATION_USER_ID);
		return (Collection<User>) getUsersQuery.execute().iterator().next();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see net.groupme.model.GroupVisualization#getUsers()
	 */
	@SuppressWarnings("unchecked")
	public Collection<User> getUsers() {
		query = "SELECT " + USERGROUPVISUALIZATION_USER_ID + " FROM "
				+ TABLE_USERGROUPVISUALIZATION + " WHERE "
				+ USERGROUPVISUALIZATION_VISUALIZATION_ID + " = " + id;
		GetUsersQuery getUsersQuery = new GetUsersQuery(ds, query,
				USERGROUPVISUALIZATION_USER_ID);
		Iterator it = getUsersQuery.execute().iterator();
		if (it.hasNext()){
			return (Collection<User>) getUsersQuery.execute().iterator().next();
		}
		return new ArrayList<User>();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see net.groupme.model.GroupVisualization#removeResourceVisualization(net.groupme.model.ResourceVisualization)
	 */
	public void removeResourceVisualization(
			ResourceVisualization resourceVisualization)
			throws DeleteFailedException {
		SqlUpdate su = new SqlUpdate(
				ds,
				"DELETE FROM " + TABLE_RESOURCEVISUALIZATION + " WHERE " //$NON-NLS-1$ //$NON-NLS-2$
						+ RESOURCEVISUALIZATION_ID
						+ " = ? AND " + RESOURCEVISUALIZATION_VISUALIZATIONID + " = ?"); //$NON-NLS-1$ //$NON-NLS-2$
		su.declareParameter(new SqlParameter("rvID", Types.INTEGER)); //$NON-NLS-1$
		su.declareParameter(new SqlParameter("gvID", Types.INTEGER)); //$NON-NLS-1$
		su.compile();
		try {
			Object[] oa = new Object[2];
			oa[0] = new Integer(resourceVisualization.getId());
			oa[1] = new Integer(this.id);
			int count = su.update(oa);
			if (count == 0)
				throw new DeleteFailedException();
		} catch (NumberFormatException e) {
			log.debug(e.getMessage());
		} 
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see net.groupme.model.GroupVisualization#removeUser(net.groupme.model.User)
	 */
	public void removeUser(User user) throws DeleteFailedException {
		SqlUpdate su = new SqlUpdate(
				ds,
				"DELETE FROM " + TABLE_USERGROUPVISUALIZATION + " WHERE " //$NON-NLS-1$ //$NON-NLS-2$
						+ USERGROUPVISUALIZATION_USER_ID
						+ " = ? AND "
						+ USERGROUPVISUALIZATION_VISUALIZATION_ID
						+ " = ? AND " + USERGROUPVISUALIZATION_GROUP_ID + " = ?"); //$NON-NLS-1$ //$NON-NLS-2$
		su.declareParameter(new SqlParameter("uID", Types.INTEGER)); //$NON-NLS-1$
		su.declareParameter(new SqlParameter("vID", Types.INTEGER)); //$NON-NLS-1$
		su.declareParameter(new SqlParameter("gID", Types.INTEGER)); //$NON-NLS-1$
		su.compile();
		try {
			Object[] oa = new Object[3];
			oa[0] = new Integer(user.getId());
			oa[1] = new Integer(this.id);
			oa[2] = new Integer(this.getGroup().getId());
			int count = su.update(oa);
			if (count == 0)
				throw new DeleteFailedException();
		} catch (NumberFormatException e) {
			log.debug(e.getMessage());
		} 
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see net.groupme.model.Identifiable#setDescription(java.lang.String)
	 */
	public void setDescription(String description) throws UpdateFailedException {
		SqlUpdate su = new SqlUpdate(
				ds,
				"UPDATE " + TABLE_VISUALIZATION + " SET " + VISUALIZATION_DESCRIPTION + " = ? WHERE " + VISUALIZATION_ID + " = ?"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
		su.declareParameter(new SqlParameter("description", Types.VARCHAR)); //$NON-NLS-1$
		su.declareParameter(new SqlParameter("ID", Types.INTEGER)); //$NON-NLS-1$
		su.compile();
		Object[] oa = new Object[2];
		oa[0] = description;
		oa[1] = new Integer(this.id);
		int count = su.update(oa);
		if (count == 0)
			throw new UpdateFailedException();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see net.groupme.model.GroupVisualization#setGroup(net.groupme.model.Group)
	 */
	public void setGroup(Group group) {
		// no need to be implemented

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see net.groupme.model.Identifiable#setLabel(java.lang.String)
	 */
	public void setLabel(String label) {
		// no need to be implemented

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see net.groupme.model.GroupVisualization#setName(java.lang.String)
	 */
	public void setName(String name) throws UpdateFailedException {
		SqlUpdate su = new SqlUpdate(
				ds,
				"UPDATE " + TABLE_VISUALIZATION + " SET " + VISUALIZATION_NAME + " = ? WHERE " + VISUALIZATION_ID + " = ?"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
		su.declareParameter(new SqlParameter("name", Types.VARCHAR)); //$NON-NLS-1$
		su.declareParameter(new SqlParameter("ID", Types.INTEGER)); //$NON-NLS-1$
		su.compile();
		Object[] oa = new Object[2];
		oa[0] = name;
		oa[1] = new Integer(this.id);
		int count = su.update(oa);
		if (count == 0)
			throw new UpdateFailedException();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see net.groupme.model.GroupVisualization#setStyle(net.groupme.model.Style)
	 */
	public void setStyle(Style style) throws UpdateFailedException {
		SqlUpdate su = new SqlUpdate(
				ds,
				"UPDATE " + TABLE_VISUALIZATIONGROUPSTYLE + " SET " + VISUALIZATIONGROUPSTYLE_GROUPSTYLEID + " = ? WHERE " + VISUALIZATIONGROUPSTYLE_VISUALIZATIONID + " = ?"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
		su.declareParameter(new SqlParameter("sID", Types.VARCHAR)); //$NON-NLS-1$
		su.declareParameter(new SqlParameter("vID", Types.INTEGER)); //$NON-NLS-1$
		su.compile();
		Object[] oa = new Object[2];
		oa[0] = style.getId();
		oa[1] = new Integer(this.id);
		int count = su.update(oa);
		if (count == 0)
			throw new UpdateFailedException();
	}

	public String getThumbnail() {
		GetStringFromTableWhereIDQuery getDescriptionQuery = new GetStringFromTableWhereIDQuery(
				ds, VISUALIZATION_THUMBNAIL, TABLE_VISUALIZATION, id);
		return (String) getDescriptionQuery.execute().iterator().next();	
	}
	
	public void setThumbnail(String url) throws UpdateFailedException {
		SqlUpdate su = new SqlUpdate(
				ds,
				"UPDATE " + TABLE_VISUALIZATION + " SET " + VISUALIZATION_THUMBNAIL + " = ? WHERE " + VISUALIZATION_ID + " = ?"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
		su.declareParameter(new SqlParameter("thumbnail", Types.VARCHAR)); //$NON-NLS-1$
		su.declareParameter(new SqlParameter("ID", Types.INTEGER)); //$NON-NLS-1$
		su.compile();
		Object[] oa = new Object[2];
		oa[0] = url;
		oa[1] = new Integer(this.id);
		int count = su.update(oa);
		if (count == 0)
			throw new UpdateFailedException();		
	}

}
